package com.situ.company.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.ResourceBundle;

//工具类
public final class JDBCUtil {// DBTool
	
	private JDBCUtil() {
		
	}
	
	private static ResourceBundle rb = ResourceBundle.getBundle("com.situ.company.util.jdbc");
	
	private static String driver = rb.getString("driver");
	private static String url = rb.getString("url");
	private static String user = rb.getString("user");
	private static String pass = rb.getString("pass");
	
	static {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url,user,pass);
	}
	
	public static Connection getConnection1()  {
		try {
			return DriverManager.getConnection(url,user,pass);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 执行DML语句
	 * 
	 * @param sql
	 * @param values
	 * @return Integer
	 */
	public static Integer update(String sql,Object... values) {
		return update(sql,Arrays.asList(values));
	}
	// 数组中元素类型是引用类型 =>asList返回的list中元素就是数组中的元素
	// 数组中元素类型是基本数据类型 =>asList返回的list中元素只有一个，即传入进来的这个数组
	
	/**
	 *执行DML语句 
	 * @param sql
	 * @param values
	 * @return Integer
	 */
	public static Integer update(String sql,List<Object> values) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			for(int i = 0;i<values.size();i++) {
				ps.setObject(i + 1, values.get(i));
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	
	/**执行DQL
	 * 
	 * @param <T> - 返回集合中的元素类型（即表对应的实体类）
	 * @param sql -即将要执行的sql
	 * @param values - 执行的sql中的数据（？）
	 * @param clazz - 即将反射得到的实体类的描述
	 * @param fields - 映射关系key=属性名 value=字段名
	 * @return List<T> - 返回查询得到的记录对象的集合
	 */
	public static <T> List<T> queryList(String sql,List<Object> values,Class<T> clazz,Map<String, String> fields) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<T> result = new ArrayList<>();
		
		try {
			conn=getConnection();
			ps = conn.prepareStatement(sql);
			for(int i = 0;i<values.size();i++) {
				ps.setObject(i + 1, values.get(i));
			}
			rs=ps.executeQuery();
			while(rs.next()){
				T model = clazz.newInstance();
				for(Entry<String, String> entry : fields.entrySet()) {
					Field dield= clazz.getDeclaredField(entry.getKey());
					dield.setAccessible(true);
					dield.set(model, rs.getObject(entry.getValue()));
				}
				result.add(model);
			}
		} catch (SQLException | InstantiationException | IllegalAccessException | NoSuchFieldException | SecurityException e) {
			e.printStackTrace();
		}finally {
			close(conn, ps, rs);
		}
		return result;
	}
	
	/**
	 * 执行sql，字段名与实体类的属性名一样的情况
	 * 
	 * @param <T>
	 * @param sql
	 * @param values
	 * @param clazz
	 * @param props
	 * @return
	 */
	public static <T> List<T> queryList(String sql,List<Object> values,Class<T> clazz,List<String> props){
		Map<String, String> fields = new HashMap<>();
		for(String p : props) {
			fields.put(p, p);
		}
		return queryList(sql, values, clazz, fields);
	}
	/**
	 * 执行dql-实体类的属性名与表的字段名一样的情况
	 * 
	 * @param <T>
	 * @param sql
	 * @param values
	 * @param clazz
	 * @return
	 */
	public static <T> List<T> queryList(String sql,List<Object> values,Class<T> clazz){
		Map<String, String> fields = new HashMap<>();
		for(Field p : clazz.getDeclaredFields()) {
			fields.put(p.getName(), p.getName());
		}
		return queryList(sql, values, clazz, fields);
	}
	
	/**
	 * 执行dql，返回一个结果对象
	 * 
	 * @param <T>
	 * @param sql
	 * @param values
	 * @param clazz
	 * @param fields
	 * @return
	 */
	public static <T> T queryModel(String sql,List<Object> values,Class<T> clazz,Map<String, String> fields){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn=getConnection();
			ps = conn.prepareStatement(sql);
			for(int i = 0;i<values.size();i++) {
				ps.setObject(i + 1, values.get(i));
			}
			rs=ps.executeQuery();
			if(rs.next()){
				T model = clazz.newInstance();
				for(Entry<String, String> entry : fields.entrySet()) {
					Field dield= clazz.getDeclaredField(entry.getKey());
					dield.setAccessible(true);
					dield.set(model, rs.getObject(entry.getValue()));
				}
				return model;
			}
		} catch (SQLException | InstantiationException | IllegalAccessException | NoSuchFieldException | SecurityException e) {
			e.printStackTrace();
		}finally {
			close(conn, ps, rs);
		}
		return null;
	}
	
	public static <T> T queryModel(String sql,List<Object> values,Class<T> clazz,List<String> props) {
		Map<String, String> fields = new HashMap<>();
		for(String p : props) {
			fields.put(p, p);
		}
		return queryModel(sql, values, clazz, fields);
	}
	
	public static <T> T queryModel(String sql,List<Object> values,Class<T> clazz) {
		Map<String, String> fields = new HashMap<>();
		for(Field p : clazz.getDeclaredFields()) {
			fields.put(p.getName(), p.getName());
		}
		return queryModel(sql, values, clazz, fields);
	}
	
	/**
	 * 根据条件查询得到记录条数
	 * 
	 * @param sql
	 * @param values
	 * @return
	 */
	public static Integer queryInt(String sql,List<Object> values) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			for(int i = 0;i<values.size();i++) {
				ps.setObject(i + 1, values.get(i));
			}
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	
	/**
	 * 关闭连接
	 * 
	 * @param conn
	 * @param st
	 */
	public static void close(Connection conn,Statement st) {
		close(conn, st,null);
	}
	
	public static void close(Connection conn,Statement st,ResultSet rs) {
		try {
			if(rs != null) {
				rs.close();
				rs = null;
			}
			if(st != null) {
				st.close();
				st = null;
			}
			if(conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) throws SQLException {
		Connection conn = JDBCUtil.getConnection();
		System.out.println(conn);	
		JDBCUtil.close(conn, null); 
	}
	

}






















 